SQL Monitor Report

A comprehensive execution view for OceanBase parallel query.

Report generate at  2022-12-22 17:51:02
Tenant SQL compatibility mode is oracle
TRACE_ID QUERY_SQL RETURN_ROWS REQUEST_TIME REQUEST_END_TIME ELAPSED_TIME TENANT_NAME USER_NAME DB_NAME PLAN_ID TENANT_ID ORACLE_VERSION SVR_IP SVR_PORT
YCB4C0BA2D919-0005F05350C624FB-0-0 select * /*+query61_x1*/ from (select promotions,total,cast(promotions as decimal(15,4))/cast(total as decimal(15,4))*100 from (select sum(ss_ext_sales_price) promotions from store_sales ,store ,promotion ,date_dim ,customer ,customer_address ,item where ss_sold_date_sk = d_date_sk and ss_store_sk = s_store_sk and ss_promo_sk = p_promo_sk and ss_customer_sk= c_customer_sk and ca_address_sk = c_current_addr_sk and ss_item_sk = i_item_sk and ca_gmt_offset = -7 and i_category = 'Books' and (p_channel_dmail = 'Y' or p_channel_email = 'Y' or p_channel_tv = 'Y') and s_gmt_offset = -7 and d_year = 1999 and d_moy = 11) promotional_sales, (select sum(ss_ext_sales_price) total from store_sales ,store ,date_dim ,customer ,customer_address ,item where ss_sold_date_sk = d_date_sk and ss_store_sk = s_store_sk and ss_customer_sk= c_customer_sk and ca_address_sk = c_current_addr_sk and ss_item_sk = i_item_sk and ca_gmt_offset = -7 and i_category = 'Books' and s_gmt_offset = -7 and d_year = 1999 and d_moy = 11) all_sales order by promotions, total ) where rownum <= 100 1 1671702640488555 1671702643775499 3286944 tpc TPCDS TPCDS 533 1006 OceanBase 4.1.0.0 (r1-93da5bca4727ea33a5c99ad5c9acfe0e8b0c2d1e) (Built Dec 21 2022 17:07:38) 192.168.1.25 52044 
select * from sys.gv$ob_plan_cache_plan_explain where tenant_id = 1006 and plan_id = 533  and svr_ip = '192.168.1.25' and svr_port = 52044
+-----------+---------------+----------+---------+------------+--------------+---------------------------------------------+------------------+----------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TENANT_ID | SVR_IP        | SVR_PORT | PLAN_ID | PLAN_DEPTH | PLAN_LINE_ID | OPERATOR                                    | NAME             | ROWS     | COST   | PROPERTY                                                                                                                                                                                                                                                                         |
+-----------+---------------+----------+---------+------------+--------------+---------------------------------------------+------------------+----------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1006      | 192.168.1.25 | 52044    | 533     | 0          | 0            | PHY_LIMIT                                   | NULL             | 1        | 917680 | NULL                                                                                                                                                                                                                                                                             |
| 1006      | 192.168.1.25 | 52044    | 533     | 1          | 1            |  PHY_NESTED_LOOP_JOIN                       | NULL             | 1        | 917680 | NULL                                                                                                                                                                                                                                                                             |
| 1006      | 192.168.1.25 | 52044    | 533     | 2          | 2            |   PHY_SUBPLAN_SCAN                          | NULL             | 1        | 304684 | NULL                                                                                                                                                                                                                                                                             |
| 1006      | 192.168.1.25 | 52044    | 533     | 3          | 3            |    PHY_SCALAR_AGGREGATE                     | NULL             | 1        | 304684 | NULL                                                                                                                                                                                                                                                                             |
| 1006      | 192.168.1.25 | 52044    | 533     | 4          | 4            |     PHY_PX_FIFO_COORD                       | NULL             | 116367   | 302575 | NULL                                                                                                                                                                                                                                                                             |
| 1006      | 192.168.1.25 | 52044    | 533     | 5          | 5            |      PHY_PX_REDUCE_TRANSMIT                 | NULL             | 116367   | 280264 | NULL                                                                                                                                                                                                                                                                             |
| 1006      | 192.168.1.25 | 52044    | 533     | 6          | 6            |       PHY_MERGE_GROUP_BY                    | NULL             | 116367   | 280122 | NULL                                                                                                                                                                                                                                                                             |
| 1006      | 192.168.1.25 | 52044    | 533     | 7          | 7            |        PHY_HASH_JOIN                        | NULL             | 366282   | 280088 | NULL                                                                                                                                                                                                                                                                             |
| 1006      | 192.168.1.25 | 52044    | 533     | 8          | 8            |         PHY_JOIN_FILTER                     | NULL             | 969964   | 891    | NULL                                                                                                                                                                                                                                                                             |
| 1006      | 192.168.1.25 | 52044    | 533     | 9          | 9            |          PHY_GRANULE_ITERATOR               | NULL             | 969964   | 891    | NULL                                                                                                                                                                                                                                                                             |
| 1006      | 192.168.1.25 | 52044    | 533     | 10         | 10           |           PHY_TABLE_SCAN                    | CUSTOMER_ADDRESS | 969964   | 891    | table_rows:6000000, physical_range_rows:6000000, logical_range_rows:6000000, index_back_rows:0, output_rows:969963, est_method:basic_stat, avaiable_index_name[CUSTOMER_ADDRESS]                                                                                                 |
| 1006      | 192.168.1.25 | 52044    | 533     | 8          | 11           |         PHY_PX_FIFO_RECEIVE                 | NULL             | 1983153  | 278395 | NULL                                                                                                                                                                                                                                                                             |
| 1006      | 192.168.1.25 | 52044    | 533     | 9          | 12           |          PHY_PX_REPART_TRANSMIT             | NULL             | 1983153  | 276077 | NULL                                                                                                                                                                                                                                                                             |
| 1006      | 192.168.1.25 | 52044    | 533     | 10         | 13           |           PHY_HASH_JOIN                     | NULL             | 1983153  | 270876 | NULL                                                                                                                                                                                                                                                                             |
| 1006      | 192.168.1.25 | 52044    | 533     | 11         | 14           |            PHY_JOIN_FILTER                  | NULL             | 2115228  | 268016 | NULL                                                                                                                                                                                                                                                                             |
| 1006      | 192.168.1.25 | 52044    | 533     | 12         | 15           |             PHY_PX_FIFO_RECEIVE             | NULL             | 2115228  | 268016 | NULL                                                                                                                                                                                                                                                                             |
| 1006      | 192.168.1.25 | 52044    | 533     | 13         | 16           |              PHY_PX_REPART_TRANSMIT         | NULL             | 2115228  | 265543 | NULL                                                                                                                                                                                                                                                                             |
| 1006      | 192.168.1.25 | 52044    | 533     | 14         | 17           |               PHY_MATERIAL                  | NULL             | 2115228  | 259995 | NULL                                                                                                                                                                                                                                                                             |
| 1006      | 192.168.1.25 | 52044    | 533     | 15         | 18           |                PHY_HASH_JOIN                | NULL             | 2115228  | 259995 | NULL                                                                                                                                                                                                                                                                             |
| 1006      | 192.168.1.25 | 52044    | 533     | 16         | 19           |                 PHY_JOIN_FILTER             | NULL             | 29930    | 249    | NULL                                                                                                                                                                                                                                                                             |
| 1006      | 192.168.1.25 | 52044    | 533     | 17         | 20           |                  PHY_PX_FIFO_RECEIVE        | NULL             | 29930    | 249    | NULL                                                                                                                                                                                                                                                                             |
| 1006      | 192.168.1.25 | 52044    | 533     | 18         | 21           |                   PHY_PX_DIST_TRANSMIT      | NULL             | 29930    | 194    | NULL                                                                                                                                                                                                                                                                             |
| 1006      | 192.168.1.25 | 52044    | 533     | 19         | 22           |                    PHY_GRANULE_ITERATOR     | NULL             | 29930    | 70     | NULL                                                                                                                                                                                                                                                                             |
| 1006      | 192.168.1.25 | 52044    | 533     | 20         | 23           |                     PHY_TABLE_SCAN          | ITEM             | 29930    | 70     | table_rows:300000, physical_range_rows:300000, logical_range_rows:300000, index_back_rows:0, output_rows:29929, est_method:remote_storage, avaiable_index_name[ITEM], estimation info[table_id:506318, (table_type:10, version:-1--1--1, logical_rc:300000, physical_rc:300000)] |
| 1006      | 192.168.1.25 | 52044    | 533     | 16         | 24           |                 PHY_HASH_JOIN               | NULL             | 20572599 | 258733 | NULL                                                                                                                                                                                                                                                                             |
| 1006      | 192.168.1.25 | 52044    | 533     | 17         | 25           |                  PHY_JOIN_FILTER            | NULL             | 30       | 13     | NULL                                                                                                                                                                                                                                                                             |
| 1006      | 192.168.1.25 | 52044    | 533     | 18         | 26           |                   PHY_PX_FIFO_RECEIVE       | NULL             | 30       | 13     | NULL                                                                                                                                                                                                                                                                             |
| 1006      | 192.168.1.25 | 52044    | 533     | 19         | 27           |                    PHY_PX_DIST_TRANSMIT     | NULL             | 30       | 13     | NULL                                                                                                                                                                                                                                                                             |
| 1006      | 192.168.1.25 | 52044    | 533     | 20         | 28           |                     PHY_GRANULE_ITERATOR    | NULL             | 30       | 13     | NULL                                                                                                                                                                                                                                                                             |
| 1006      | 192.168.1.25 | 52044    | 533     | 21         | 29           |                      PHY_TABLE_SCAN         | DATE_DIM         | 30       | 13     | table_rows:73049, physical_range_rows:73049, logical_range_rows:73049, index_back_rows:0, output_rows:29, est_method:remote_storage, avaiable_index_name[DATE_DIM], estimation info[table_id:506295, (table_type:10, version:-1--1--1, logical_rc:73049, physical_rc:73049)]     |
| 1006      | 192.168.1.25 | 52044    | 533     | 17         | 30           |                  PHY_PX_FIFO_RECEIVE        | NULL             | 2214899  | 258663 | NULL                                                                                                                                                                                                                                                                             |
| 1006      | 192.168.1.25 | 52044    | 533     | 18         | 31           |                   PHY_PX_DIST_TRANSMIT      | NULL             | 2214899  | 253681 | NULL                                                                                                                                                                                                                                                                             |
| 1006      | 192.168.1.25 | 52044    | 533     | 19         | 32           |                    PHY_HASH_JOIN            | NULL             | 2214899  | 242483 | NULL                                                                                                                                                                                                                                                                             |
| 1006      | 192.168.1.25 | 52044    | 533     | 20         | 33           |                     PHY_PX_FIFO_RECEIVE     | NULL             | 1500     | 19     | NULL                                                                                                                                                                                                                                                                             |
| 1006      | 192.168.1.25 | 52044    | 533     | 21         | 34           |                      PHY_PX_DIST_TRANSMIT   | NULL             | 1500     | 6      | NULL                                                                                                                                                                                                                                                                             |
| 1006      | 192.168.1.25 | 52044    | 533     | 22         | 35           |                       PHY_GRANULE_ITERATOR  | NULL             | 1500     | 0      | NULL                                                                                                                                                                                                                                                                             |
| 1006      | 192.168.1.25 | 52044    | 533     | 23         | 36           |                        PHY_TABLE_SCAN       | PROMOTION        | 1500     | 0      | table_rows:1500, physical_range_rows:1500, logical_range_rows:1500, index_back_rows:0, output_rows:1499, est_method:remote_storage, avaiable_index_name[PROMOTION], estimation info[table_id:511605, (table_type:10, version:-1--1--1, logical_rc:1500, physical_rc:1500)]       |
| 1006      | 192.168.1.25 | 52044    | 533     | 20         | 37           |                     PHY_HASH_JOIN           | NULL             | 2370491  | 241840 | NULL                                                                                                                                                                                                                                                                             |
| 1006      | 192.168.1.25 | 52044    | 533     | 21         | 38           |                      PHY_PX_FIFO_RECEIVE    | NULL             | 249      | 2      | NULL                                                                                                                                                                                                                                                                             |
| 1006      | 192.168.1.25 | 52044    | 533     | 22         | 39           |                       PHY_PX_DIST_TRANSMIT  | NULL             | 249      | 0      | NULL                                                                                                                                                                                                                                                                             |
| 1006      | 192.168.1.25 | 52044    | 533     | 23         | 40           |                        PHY_GRANULE_ITERATOR | NULL             | 249      | 0      | NULL                                                                                                                                                                                                                                                                             |
| 1006      | 192.168.1.25 | 52044    | 533     | 24         | 41           |                         PHY_TABLE_SCAN      | STORE            | 249      | 0      | table_rows:1002, physical_range_rows:1002, logical_range_rows:1002, index_back_rows:0, output_rows:249, est_method:remote_storage, avaiable_index_name[STORE], estimation info[table_id:506322, (table_type:10, version:-1--1--1, logical_rc:1002, physical_rc:1002)]            |
| 1006      | 192.168.1.25 | 52044    | 533     | 21         | 42           |                      PHY_JOIN_FILTER        | NULL             | 5004202  | 240532 | NULL                                                                                                                                                                                                                                                                             |
| 1006      | 192.168.1.25 | 52044    | 533     | 22         | 43           |                       PHY_JOIN_FILTER       | NULL             | 5004202  | 240532 | NULL                                                                                                                                                                                                                                                                             |
| 1006      | 192.168.1.25 | 52044    | 533     | 23         | 44           |                        PHY_GRANULE_ITERATOR | NULL             | 5004202  | 240532 | NULL                                                                                                                                                                                                                                                                             |
| 1006      | 192.168.1.25 | 52044    | 533     | 24         | 45           |                         PHY_TABLE_SCAN      | STORE_SALES      | 5004202  | 240532 | table_rows:2879987999, physical_range_rows:2879987999, logical_range_rows:2879987999, index_back_rows:0, output_rows:2879987999, est_method:basic_stat, avaiable_index_name[STORE_SALES]                                                                                         |
| 1006      | 192.168.1.25 | 52044    | 533     | 11         | 46           |            PHY_JOIN_FILTER                  | NULL             | 1994722  | 881    | NULL                                                                                                                                                                                                                                                                             |
| 1006      | 192.168.1.25 | 52044    | 533     | 12         | 47           |             PHY_GRANULE_ITERATOR            | NULL             | 1994722  | 881    | NULL                                                                                                                                                                                                                                                                             |
| 1006      | 192.168.1.25 | 52044    | 533     | 13         | 48           |              PHY_TABLE_SCAN                 | CUSTOMER         | 1994722  | 881    | table_rows:12000000, physical_range_rows:12000000, logical_range_rows:12000000, index_back_rows:0, output_rows:12000000, est_method:basic_stat, avaiable_index_name[CUSTOMER]                                                                                                    |
| 1006      | 192.168.1.25 | 52044    | 533     | 2          | 49           |   PHY_SUBPLAN_SCAN                          | NULL             | 1        | 306473 | NULL                                                                                                                                                                                                                                                                             |
| 1006      | 192.168.1.25 | 52044    | 533     | 3          | 50           |    PHY_SCALAR_AGGREGATE                     | NULL             | 1        | 306473 | NULL                                                                                                                                                                                                                                                                             |
| 1006      | 192.168.1.25 | 52044    | 533     | 4          | 51           |     PHY_PX_FIFO_COORD                       | NULL             | 117530   | 304342 | NULL                                                                                                                                                                                                                                                                             |
| 1006      | 192.168.1.25 | 52044    | 533     | 5          | 52           |      PHY_PX_REDUCE_TRANSMIT                 | NULL             | 117530   | 281809 | NULL                                                                                                                                                                                                                                                                             |
| 1006      | 192.168.1.25 | 52044    | 533     | 6          | 53           |       PHY_MERGE_GROUP_BY                    | NULL             | 117530   | 281665 | NULL                                                                                                                                                                                                                                                                             |
| 1006      | 192.168.1.25 | 52044    | 533     | 7          | 54           |        PHY_HASH_JOIN                        | NULL             | 392012   | 281629 | NULL                                                                                                                                                                                                                                                                             |
| 1006      | 192.168.1.25 | 52044    | 533     | 8          | 55           |         PHY_JOIN_FILTER                     | NULL             | 969964   | 891    | NULL                                                                                                                                                                                                                                                                             |
| 1006      | 192.168.1.25 | 52044    | 533     | 9          | 56           |          PHY_GRANULE_ITERATOR               | NULL             | 969964   | 891    | NULL                                                                                                                                                                                                                                                                             |
| 1006      | 192.168.1.25 | 52044    | 533     | 10         | 57           |           PHY_TABLE_SCAN                    | CUSTOMER_ADDRESS | 969964   | 891    | table_rows:6000000, physical_range_rows:6000000, logical_range_rows:6000000, index_back_rows:0, output_rows:969963, est_method:basic_stat, avaiable_index_name[CUSTOMER_ADDRESS]                                                                                                 |
| 1006      | 192.168.1.25 | 52044    | 533     | 8          | 58           |         PHY_PX_FIFO_RECEIVE                 | NULL             | 2122464  | 279927 | NULL                                                                                                                                                                                                                                                                             |
| 1006      | 192.168.1.25 | 52044    | 533     | 9          | 59           |          PHY_PX_REPART_TRANSMIT             | NULL             | 2122464  | 277446 | NULL                                                                                                                                                                                                                                                                             |
| 1006      | 192.168.1.25 | 52044    | 533     | 10         | 60           |           PHY_HASH_JOIN                     | NULL             | 2122464  | 271879 | NULL                                                                                                                                                                                                                                                                             |
| 1006      | 192.168.1.25 | 52044    | 533     | 11         | 61           |            PHY_JOIN_FILTER                  | NULL             | 2263817  | 268916 | NULL                                                                                                                                                                                                                                                                             |
| 1006      | 192.168.1.25 | 52044    | 533     | 12         | 62           |             PHY_PX_FIFO_RECEIVE             | NULL             | 2263817  | 268916 | NULL                                                                                                                                                                                                                                                                             |
| 1006      | 192.168.1.25 | 52044    | 533     | 13         | 63           |              PHY_PX_REPART_TRANSMIT         | NULL             | 2263817  | 266270 | NULL                                                                                                                                                                                                                                                                             |
| 1006      | 192.168.1.25 | 52044    | 533     | 14         | 64           |               PHY_MATERIAL                  | NULL             | 2263817  | 260332 | NULL                                                                                                                                                                                                                                                                             |
| 1006      | 192.168.1.25 | 52044    | 533     | 15         | 65           |                PHY_HASH_JOIN                | NULL             | 2263817  | 260332 | NULL                                                                                                                                                                                                                                                                             |
| 1006      | 192.168.1.25 | 52044    | 533     | 16         | 66           |                 PHY_JOIN_FILTER             | NULL             | 29930    | 249    | NULL                                                                                                                                                                                                                                                                             |
| 1006      | 192.168.1.25 | 52044    | 533     | 17         | 67           |                  PHY_PX_FIFO_RECEIVE        | NULL             | 29930    | 249    | NULL                                                                                                                                                                                                                                                                             |
| 1006      | 192.168.1.25 | 52044    | 533     | 18         | 68           |                   PHY_PX_DIST_TRANSMIT      | NULL             | 29930    | 194    | NULL                                                                                                                                                                                                                                                                             |
| 1006      | 192.168.1.25 | 52044    | 533     | 19         | 69           |                    PHY_GRANULE_ITERATOR     | NULL             | 29930    | 70     | NULL                                                                                                                                                                                                                                                                             |
| 1006      | 192.168.1.25 | 52044    | 533     | 20         | 70           |                     PHY_TABLE_SCAN          | ITEM             | 29930    | 70     | table_rows:300000, physical_range_rows:300000, logical_range_rows:300000, index_back_rows:0, output_rows:29929, est_method:remote_storage, avaiable_index_name[ITEM], estimation info[table_id:506318, (table_type:10, version:-1--1--1, logical_rc:300000, physical_rc:300000)] |
| 1006      | 192.168.1.25 | 52044    | 533     | 16         | 71           |                 PHY_HASH_JOIN               | NULL             | 22017773 | 259001 | NULL                                                                                                                                                                                                                                                                             |
| 1006      | 192.168.1.25 | 52044    | 533     | 17         | 72           |                  PHY_JOIN_FILTER            | NULL             | 30       | 13     | NULL                                                                                                                                                                                                                                                                             |
| 1006      | 192.168.1.25 | 52044    | 533     | 18         | 73           |                   PHY_PX_FIFO_RECEIVE       | NULL             | 30       | 13     | NULL                                                                                                                                                                                                                                                                             |
| 1006      | 192.168.1.25 | 52044    | 533     | 19         | 74           |                    PHY_PX_DIST_TRANSMIT     | NULL             | 30       | 13     | NULL                                                                                                                                                                                                                                                                             |
| 1006      | 192.168.1.25 | 52044    | 533     | 20         | 75           |                     PHY_GRANULE_ITERATOR    | NULL             | 30       | 13     | NULL                                                                                                                                                                                                                                                                             |
| 1006      | 192.168.1.25 | 52044    | 533     | 21         | 76           |                      PHY_TABLE_SCAN         | DATE_DIM         | 30       | 13     | table_rows:73049, physical_range_rows:73049, logical_range_rows:73049, index_back_rows:0, output_rows:29, est_method:remote_storage, avaiable_index_name[DATE_DIM], estimation info[table_id:506295, (table_type:10, version:-1--1--1, logical_rc:73049, physical_rc:73049)]     |
| 1006      | 192.168.1.25 | 52044    | 533     | 17         | 77           |                  PHY_PX_FIFO_RECEIVE        | NULL             | 2370491  | 258927 | NULL                                                                                                                                                                                                                                                                             |
| 1006      | 192.168.1.25 | 52044    | 533     | 18         | 78           |                   PHY_PX_DIST_TRANSMIT      | NULL             | 2370491  | 253595 | NULL                                                                                                                                                                                                                                                                             |
| 1006      | 192.168.1.25 | 52044    | 533     | 19         | 79           |                    PHY_HASH_JOIN            | NULL             | 2370491  | 241611 | NULL                                                                                                                                                                                                                                                                             |
| 1006      | 192.168.1.25 | 52044    | 533     | 20         | 80           |                     PHY_PX_FIFO_RECEIVE     | NULL             | 249      | 2      | NULL                                                                                                                                                                                                                                                                             |
| 1006      | 192.168.1.25 | 52044    | 533     | 21         | 81           |                      PHY_PX_DIST_TRANSMIT   | NULL             | 249      | 0      | NULL                                                                                                                                                                                                                                                                             |
| 1006      | 192.168.1.25 | 52044    | 533     | 22         | 82           |                       PHY_GRANULE_ITERATOR  | NULL             | 249      | 0      | NULL                                                                                                                                                                                                                                                                             |
| 1006      | 192.168.1.25 | 52044    | 533     | 23         | 83           |                        PHY_TABLE_SCAN       | STORE            | 249      | 0      | table_rows:1002, physical_range_rows:1002, logical_range_rows:1002, index_back_rows:0, output_rows:249, est_method:remote_storage, avaiable_index_name[STORE], estimation info[table_id:506322, (table_type:10, version:-1--1--1, logical_rc:1002, physical_rc:1002)]            |
| 1006      | 192.168.1.25 | 52044    | 533     | 20         | 84           |                     PHY_JOIN_FILTER         | NULL             | 5004202  | 240303 | NULL                                                                                                                                                                                                                                                                             |
| 1006      | 192.168.1.25 | 52044    | 533     | 21         | 85           |                      PHY_JOIN_FILTER        | NULL             | 5004202  | 240303 | NULL                                                                                                                                                                                                                                                                             |
| 1006      | 192.168.1.25 | 52044    | 533     | 22         | 86           |                       PHY_GRANULE_ITERATOR  | NULL             | 5004202  | 240303 | NULL                                                                                                                                                                                                                                                                             |
| 1006      | 192.168.1.25 | 52044    | 533     | 23         | 87           |                        PHY_TABLE_SCAN       | STORE_SALES      | 5004202  | 240303 | table_rows:2879987999, physical_range_rows:2879987999, logical_range_rows:2879987999, index_back_rows:0, output_rows:2879987999, est_method:basic_stat, avaiable_index_name[STORE_SALES]                                                                                         |
| 1006      | 192.168.1.25 | 52044    | 533     | 11         | 88           |            PHY_JOIN_FILTER                  | NULL             | 1994722  | 881    | NULL                                                                                                                                                                                                                                                                             |
| 1006      | 192.168.1.25 | 52044    | 533     | 12         | 89           |             PHY_GRANULE_ITERATOR            | NULL             | 1994722  | 881    | NULL                                                                                                                                                                                                                                                                             |
| 1006      | 192.168.1.25 | 52044    | 533     | 13         | 90           |              PHY_TABLE_SCAN                 | CUSTOMER         | 1994722  | 881    | table_rows:12000000, physical_range_rows:12000000, logical_range_rows:12000000, index_back_rows:0, output_rows:12000000, est_method:basic_stat, avaiable_index_name[CUSTOMER]                                                                                                    |
+-----------+---------------+----------+---------+------------+--------------+---------------------------------------------+------------------+----------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

SCHEMA INFO (click to expand)

SQL Audit Info (click to expand)

SQL_PLAN_MONITOR DFO LEVEL VIEW

DFO Scheduling timeline view

DFO data output timeline

DB Time View

SQL_PLAN_MONITOR SQC LEVEL VIEW

Goto Operator Order Goto Server Order

View Ordered by Operator

View Ordered by Server

SQL_PLAN_MONITOR DETAIL VIEW

Goto Operator Order Goto Thread Order

Large View, May SLOWDOWN Your Browser! Click to Expand View Ordered by Operator

Large View, May SLOWDOWN Your Browser! Click to Expand View Ordered by Thread.

SQL Used to Generate This Report SQL

Please set following session variables first in order to execute these SQLs correctly
SET nls_timestamp_format = 'YYYY-MM-DD HH24:MI:SS.FF';
SET nls_date_format = 'YYYY-MM-DD HH24:MI:SS';

DFO LEVEL
SELECT
  AVG("ROWS") EST_ROWS, /*0 */
  plan_monitor.PLAN_DEPTH,
  plan_monitor.PLAN_LINE_ID,
  PLAN_OPERATION,
  COUNT(*) PARALLEL,
  MIN(FIRST_REFRESH_TIME) MIN_FIRST_REFRESH_TIME, /* 5 */
  MAX(LAST_REFRESH_TIME) MAX_LAST_REFRESH_TIME,
  MIN(FIRST_CHANGE_TIME) MIN_FIRST_CHANGE_TIME,
  MAX(LAST_CHANGE_TIME) MAX_LAST_CHANGE_TIME,
  MIN(FIRST_REFRESH_TS) MIN_FIRST_REFRESH_TS,
  MAX(LAST_REFRESH_TS) MAX_LAST_REFRESH_TS, /*10 */
  MIN(FIRST_CHANGE_TS) MIN_FIRST_CHANGE_TS,
  MAX(LAST_CHANGE_TS) MAX_LAST_CHANGE_TS,
  AVG(REFRESH_TS) AVG_REFRESH_TIME,
  MAX(REFRESH_TS) MAX_REFRESH_TIME,
  MIN(REFRESH_TS) MIN_REFRESH_TIME,/*15 */
  AVG(CHANGE_TS) AVG_CHANGE_TIME,
  MAX(CHANGE_TS) MAX_CHANGE_TIME,
  MIN(CHANGE_TS) MIN_CHANGE_TIME,
  SUM(OUTPUT_ROWS) TOTAL_OUTPUT_ROWS,
  (MAX(LAST_CHANGE_TS-FIRST_CHANGE_TS) - MIN(LAST_CHANGE_TS-FIRST_CHANGE_TS)) / MAX(LAST_CHANGE_TS-FIRST_CHANGE_TS+0.000000001) SKEWNESS,
  SUM(STARTS) TOTAL_RESCAN_TIMES,/* 20 */
  MAX(OTHERSTAT_1_ID) OTHERSTAT_1_ID,
  SUM(OTHERSTAT_1_VALUE) SUM_STAT_1,
  MAX(OTHERSTAT_1_VALUE) MAX_STAT_1,
  MIN(OTHERSTAT_1_VALUE) MIN_STAT_1,
  AVG(OTHERSTAT_1_VALUE) AVG_STAT_1, /* 25 */
  MAX(OTHERSTAT_2_ID) OTHERSTAT_2_ID,
  SUM(OTHERSTAT_2_VALUE) SUM_STAT_2,
  MAX(OTHERSTAT_2_VALUE) MAX_STAT_2,
  MIN(OTHERSTAT_2_VALUE) MIN_STAT_2,
  AVG(OTHERSTAT_2_VALUE) AVG_STAT_2, /* 30 */
  MAX(OTHERSTAT_3_ID) OTHERSTAT_3_ID,
  SUM(OTHERSTAT_3_VALUE) SUM_STAT_3,
  MAX(OTHERSTAT_3_VALUE) MAX_STAT_3,
  MIN(OTHERSTAT_3_VALUE) MIN_STAT_3,
  AVG(OTHERSTAT_3_VALUE) AVG_STAT_3, /* 35 */
  MAX(OTHERSTAT_4_ID) OTHERSTAT_4_ID,
  SUM(OTHERSTAT_4_VALUE) SUM_STAT_4,
  MAX(OTHERSTAT_4_VALUE) MAX_STAT_4,
  MIN(OTHERSTAT_4_VALUE) MIN_STAT_4,
  AVG(OTHERSTAT_4_VALUE) AVG_STAT_4, /* 40 */
  MAX(OTHERSTAT_5_ID) OTHERSTAT_5_ID,
  SUM(OTHERSTAT_5_VALUE) SUM_STAT_5,
  MAX(OTHERSTAT_5_VALUE) MAX_STAT_5,
  MIN(OTHERSTAT_5_VALUE) MIN_STAT_5,
  AVG(OTHERSTAT_5_VALUE) AVG_STAT_5, /* 45*/
  MAX(OTHERSTAT_6_ID) OTHERSTAT_6_ID,
  SUM(OTHERSTAT_6_VALUE) SUM_STAT_6,
  MAX(OTHERSTAT_6_VALUE) MAX_STAT_6,
  MIN(OTHERSTAT_6_VALUE) MIN_STAT_6,
  AVG(OTHERSTAT_6_VALUE) AVG_STAT_6 /* 50 */,
  TRUNC(AVG(DB_TIME)/1000000000.0/2.5, 4) MY_DB_TIME,
  TRUNC(AVG(DB_TIME-USER_IO_WAIT_TIME)/1000000000.0/2.5, 4) MY_CPU_TIME,
  TRUNC(AVG(USER_IO_WAIT_TIME)/1000000000.0/2.5, 4) MY_IO_TIME
FROM
(
select
  PLAN_DEPTH,
  PLAN_LINE_ID,
  PLAN_OPERATION,
  OUTPUT_ROWS,
  STARTS,
  FIRST_REFRESH_TIME,
  LAST_REFRESH_TIME,
  FIRST_CHANGE_TIME,
  LAST_CHANGE_TIME,
  OTHERSTAT_1_ID,
  OTHERSTAT_2_ID,
  OTHERSTAT_3_ID,
  OTHERSTAT_4_ID,
  OTHERSTAT_5_ID,
  OTHERSTAT_6_ID,
  OTHERSTAT_1_VALUE,
  OTHERSTAT_2_VALUE,
  OTHERSTAT_3_VALUE,
  OTHERSTAT_4_VALUE,
  OTHERSTAT_5_VALUE,
  OTHERSTAT_6_VALUE,
  24 * 3600 * extract(day FROM FIRST_REFRESH_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) + 3600 * extract(hour FROM FIRST_REFRESH_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) + 60 * extract(minute FROM FIRST_REFRESH_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) + extract(second FROM FIRST_REFRESH_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) FIRST_REFRESH_TS,
  24 * 3600 * extract(day FROM LAST_REFRESH_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) + 3600 * extract(hour FROM LAST_REFRESH_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) + 60 * extract(minute FROM LAST_REFRESH_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) + extract(second FROM LAST_REFRESH_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) LAST_REFRESH_TS,
  24 * 3600 * extract(day FROM FIRST_CHANGE_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) + 3600 * extract(hour FROM FIRST_CHANGE_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) + 60 * extract(minute FROM FIRST_CHANGE_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) + extract(second FROM FIRST_CHANGE_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) FIRST_CHANGE_TS,
  24 * 3600 * extract(day FROM LAST_CHANGE_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) + 3600 * extract(hour FROM LAST_CHANGE_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) + 60 * extract(minute FROM LAST_CHANGE_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) + extract(second FROM LAST_CHANGE_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) LAST_CHANGE_TS,
  24 * 3600 * extract(day FROM TO_TIMESTAMP(LAST_REFRESH_TIME, 'YYYY-MM-DD HH24:MI:SS.FF') - TO_TIMESTAMP(FIRST_REFRESH_TIME, 'YYYY-MM-DD HH24:MI:SS.FF')) + 3600 * extract(hour FROM TO_TIMESTAMP(LAST_REFRESH_TIME, 'YYYY-MM-DD HH24:MI:SS.FF') - TO_TIMESTAMP(FIRST_REFRESH_TIME, 'YYYY-MM-DD HH24:MI:SS.FF')) + 60 * extract(minute FROM TO_TIMESTAMP(LAST_REFRESH_TIME, 'YYYY-MM-DD HH24:MI:SS.FF') - TO_TIMESTAMP(FIRST_REFRESH_TIME, 'YYYY-MM-DD HH24:MI:SS.FF')) + extract(second FROM TO_TIMESTAMP(LAST_REFRESH_TIME, 'YYYY-MM-DD HH24:MI:SS.FF') - TO_TIMESTAMP(FIRST_REFRESH_TIME, 'YYYY-MM-DD HH24:MI:SS.FF')) REFRESH_TS,
  24 * 3600 * extract(day FROM TO_TIMESTAMP(LAST_CHANGE_TIME, 'YYYY-MM-DD HH24:MI:SS.FF') - TO_TIMESTAMP(FIRST_CHANGE_TIME, 'YYYY-MM-DD HH24:MI:SS.FF')) + 3600 * extract(hour FROM TO_TIMESTAMP(LAST_CHANGE_TIME, 'YYYY-MM-DD HH24:MI:SS.FF') - TO_TIMESTAMP(FIRST_CHANGE_TIME, 'YYYY-MM-DD HH24:MI:SS.FF')) + 60 * extract(minute FROM TO_TIMESTAMP(LAST_CHANGE_TIME, 'YYYY-MM-DD HH24:MI:SS.FF') - TO_TIMESTAMP(FIRST_CHANGE_TIME, 'YYYY-MM-DD HH24:MI:SS.FF')) + extract(second FROM TO_TIMESTAMP(LAST_CHANGE_TIME, 'YYYY-MM-DD HH24:MI:SS.FF') - TO_TIMESTAMP(FIRST_CHANGE_TIME, 'YYYY-MM-DD HH24:MI:SS.FF')) CHANGE_TS,
DB_TIME,
USER_IO_WAIT_TIME
from
  sys.gv$sql_plan_monitor
  where
    trace_id = 'YCB4C0BA2D919-0005F05350C624FB-0-0'

) plan_monitor
LEFT JOIN
(
 SELECT "ROWS", PLAN_LINE_ID FROM sys.gv$ob_plan_cache_plan_explain WHERE plan_id = 533 AND tenant_id = 1006
) plan_explain
ON
  plan_monitor.PLAN_LINE_ID = plan_explain.PLAN_LINE_ID
GROUP BY
  plan_monitor.PLAN_LINE_ID, PLAN_OPERATION, plan_monitor.PLAN_DEPTH
ORDER BY
  plan_monitor.PLAN_LINE_ID ASC;

Server LEVEL
SELECT
  PLAN_DEPTH,
  PLAN_LINE_ID,
  PLAN_OPERATION,
  COUNT(*) PARALLEL,
  MIN(FIRST_REFRESH_TIME) MIN_FIRST_REFRESH_TIME,
  MAX(LAST_REFRESH_TIME) MAX_LAST_REFRESH_TIME,
  MIN(FIRST_CHANGE_TIME) MIN_FIRST_CHANGE_TIME,
  MAX(LAST_CHANGE_TIME) MAX_LAST_CHANGE_TIME,
  MIN(FIRST_REFRESH_TS) MIN_FIRST_REFRESH_TS,
  MAX(LAST_REFRESH_TS) MAX_LAST_REFRESH_TS,
  MIN(FIRST_CHANGE_TS) MIN_FIRST_CHANGE_TS,
  MAX(LAST_CHANGE_TS) MAX_LAST_CHANGE_TS,
  AVG(REFRESH_TS) AVG_REFRESH_TIME,
  MAX(REFRESH_TS) MAX_REFRESH_TIME,
  MIN(REFRESH_TS) MIN_REFRESH_TIME,
  AVG(CHANGE_TS) AVG_CHANGE_TIME,
  MAX(CHANGE_TS) MAX_CHANGE_TIME,
  MIN(CHANGE_TS) MIN_CHANGE_TIME,
  SUM(OUTPUT_ROWS) TOTAL_OUTPUT_ROWS,
  (MAX(LAST_CHANGE_TS-FIRST_CHANGE_TS) - MIN(LAST_CHANGE_TS-FIRST_CHANGE_TS)) / MAX(LAST_CHANGE_TS-FIRST_CHANGE_TS+0.00000001) SKEWNESS,
  SUM(STARTS) TOTAL_RESCAN_TIMES,
  SVR_IP,
  SVR_PORT,
  MAX(OTHERSTAT_1_VALUE) MAX_STAT_1,
  MIN(OTHERSTAT_1_VALUE) MIN_STAT_1,
  AVG(OTHERSTAT_1_VALUE) AVG_STAT_1,
  MAX(OTHERSTAT_2_VALUE) MAX_STAT_2,
  MIN(OTHERSTAT_2_VALUE) MIN_STAT_2,
  AVG(OTHERSTAT_2_VALUE) AVG_STAT_2,
  MAX(OTHERSTAT_3_VALUE) MAX_STAT_3,
  MIN(OTHERSTAT_3_VALUE) MIN_STAT_3,
  AVG(OTHERSTAT_3_VALUE) AVG_STAT_3,
  MAX(OTHERSTAT_4_VALUE) MAX_STAT_4,
  MIN(OTHERSTAT_4_VALUE) MIN_STAT_4,
  AVG(OTHERSTAT_4_VALUE) AVG_STAT_4,
  MAX(OTHERSTAT_5_VALUE) MAX_STAT_5,
  MIN(OTHERSTAT_5_VALUE) MIN_STAT_5,
  AVG(OTHERSTAT_5_VALUE) AVG_STAT_5,
  MAX(OTHERSTAT_6_VALUE) MAX_STAT_6,
  MIN(OTHERSTAT_6_VALUE) MIN_STAT_6,
  AVG(OTHERSTAT_6_VALUE) AVG_STAT_6,
  TRUNC(AVG(DB_TIME)/1000000000.0/2.5, 4) MY_DB_TIME,
  TRUNC(AVG(DB_TIME-USER_IO_WAIT_TIME)/1000000000.0/2.5, 4) MY_CPU_TIME,
  TRUNC(AVG(USER_IO_WAIT_TIME)/1000000000.0/2.5, 4) MY_IO_TIME
FROM
(
select
  PLAN_DEPTH,
  SVR_IP,
  SVR_PORT,
  PLAN_LINE_ID,
  PLAN_OPERATION,
  OUTPUT_ROWS,
  STARTS,
  FIRST_REFRESH_TIME,
  LAST_REFRESH_TIME,
  FIRST_CHANGE_TIME,
  LAST_CHANGE_TIME,
  OTHERSTAT_1_VALUE,
  OTHERSTAT_2_VALUE,
  OTHERSTAT_3_VALUE,
  OTHERSTAT_4_VALUE,
  OTHERSTAT_5_VALUE,
  OTHERSTAT_6_VALUE,
  24 * 3600 * extract(day FROM FIRST_REFRESH_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) + 3600 * extract(hour FROM FIRST_REFRESH_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) + 60 * extract(minute FROM FIRST_REFRESH_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) + extract(second FROM FIRST_REFRESH_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) FIRST_REFRESH_TS,
  24 * 3600 * extract(day FROM LAST_REFRESH_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) + 3600 * extract(hour FROM LAST_REFRESH_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) + 60 * extract(minute FROM LAST_REFRESH_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) + extract(second FROM LAST_REFRESH_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) LAST_REFRESH_TS,
  24 * 3600 * extract(day FROM FIRST_CHANGE_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) + 3600 * extract(hour FROM FIRST_CHANGE_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) + 60 * extract(minute FROM FIRST_CHANGE_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) + extract(second FROM FIRST_CHANGE_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) FIRST_CHANGE_TS,
  24 * 3600 * extract(day FROM LAST_CHANGE_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) + 3600 * extract(hour FROM LAST_CHANGE_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) + 60 * extract(minute FROM LAST_CHANGE_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) + extract(second FROM LAST_CHANGE_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) LAST_CHANGE_TS,
  24 * 3600 * extract(day FROM TO_TIMESTAMP(LAST_REFRESH_TIME, 'YYYY-MM-DD HH24:MI:SS.FF') - TO_TIMESTAMP(FIRST_REFRESH_TIME, 'YYYY-MM-DD HH24:MI:SS.FF')) + 3600 * extract(hour FROM TO_TIMESTAMP(LAST_REFRESH_TIME, 'YYYY-MM-DD HH24:MI:SS.FF') - TO_TIMESTAMP(FIRST_REFRESH_TIME, 'YYYY-MM-DD HH24:MI:SS.FF')) + 60 * extract(minute FROM TO_TIMESTAMP(LAST_REFRESH_TIME, 'YYYY-MM-DD HH24:MI:SS.FF') - TO_TIMESTAMP(FIRST_REFRESH_TIME, 'YYYY-MM-DD HH24:MI:SS.FF')) + extract(second FROM TO_TIMESTAMP(LAST_REFRESH_TIME, 'YYYY-MM-DD HH24:MI:SS.FF') - TO_TIMESTAMP(FIRST_REFRESH_TIME, 'YYYY-MM-DD HH24:MI:SS.FF')) REFRESH_TS,
  24 * 3600 * extract(day FROM TO_TIMESTAMP(LAST_CHANGE_TIME, 'YYYY-MM-DD HH24:MI:SS.FF') - TO_TIMESTAMP(FIRST_CHANGE_TIME, 'YYYY-MM-DD HH24:MI:SS.FF')) + 3600 * extract(hour FROM TO_TIMESTAMP(LAST_CHANGE_TIME, 'YYYY-MM-DD HH24:MI:SS.FF') - TO_TIMESTAMP(FIRST_CHANGE_TIME, 'YYYY-MM-DD HH24:MI:SS.FF')) + 60 * extract(minute FROM TO_TIMESTAMP(LAST_CHANGE_TIME, 'YYYY-MM-DD HH24:MI:SS.FF') - TO_TIMESTAMP(FIRST_CHANGE_TIME, 'YYYY-MM-DD HH24:MI:SS.FF')) + extract(second FROM TO_TIMESTAMP(LAST_CHANGE_TIME, 'YYYY-MM-DD HH24:MI:SS.FF') - TO_TIMESTAMP(FIRST_CHANGE_TIME, 'YYYY-MM-DD HH24:MI:SS.FF')) CHANGE_TS,
  DB_TIME,
  USER_IO_WAIT_TIME
from
  sys.gv$sql_plan_monitor
  where
    trace_id = 'YCB4C0BA2D919-0005F05350C624FB-0-0'
) tmp
GROUP BY
  PLAN_LINE_ID,PLAN_OPERATION,PLAN_DEPTH,SVR_IP,SVR_PORT
ORDER BY
  PLAN_LINE_ID ASC, MAX_CHANGE_TIME ASC, SVR_IP, SVR_PORT;

Thread LEVEL
select
  PLAN_DEPTH, /*0*/
  SVR_IP,
  SVR_PORT,
  PROCESS_NAME,
  PLAN_LINE_ID,
  PLAN_OPERATION,/*5*/
  OUTPUT_ROWS,
  STARTS RESCAN_TIMES,
  FIRST_REFRESH_TIME,
  LAST_REFRESH_TIME,
  FIRST_CHANGE_TIME,/*10*/
  LAST_CHANGE_TIME,
  24 * 3600 * extract(day FROM FIRST_REFRESH_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) + 3600 * extract(hour FROM FIRST_REFRESH_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) + 60 * extract(minute FROM FIRST_REFRESH_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) + extract(second FROM FIRST_REFRESH_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) FIRST_REFRESH_TS,
  24 * 3600 * extract(day FROM LAST_REFRESH_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) + 3600 * extract(hour FROM LAST_REFRESH_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) + 60 * extract(minute FROM LAST_REFRESH_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) + extract(second FROM LAST_REFRESH_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) LAST_REFRESH_TS,
  24 * 3600 * extract(day FROM FIRST_CHANGE_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) + 3600 * extract(hour FROM FIRST_CHANGE_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) + 60 * extract(minute FROM FIRST_CHANGE_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) + extract(second FROM FIRST_CHANGE_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) FIRST_CHANGE_TS,
  24 * 3600 * extract(day FROM LAST_CHANGE_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) + 3600 * extract(hour FROM LAST_CHANGE_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) + 60 * extract(minute FROM LAST_CHANGE_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) + extract(second FROM LAST_CHANGE_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) LAST_CHANGE_TS,
  24 * 3600 * extract(day FROM TO_TIMESTAMP(LAST_REFRESH_TIME, 'YYYY-MM-DD HH24:MI:SS.FF') - TO_TIMESTAMP(FIRST_REFRESH_TIME, 'YYYY-MM-DD HH24:MI:SS.FF')) + 3600 * extract(hour FROM TO_TIMESTAMP(LAST_REFRESH_TIME, 'YYYY-MM-DD HH24:MI:SS.FF') - TO_TIMESTAMP(FIRST_REFRESH_TIME, 'YYYY-MM-DD HH24:MI:SS.FF')) + 60 * extract(minute FROM TO_TIMESTAMP(LAST_REFRESH_TIME, 'YYYY-MM-DD HH24:MI:SS.FF') - TO_TIMESTAMP(FIRST_REFRESH_TIME, 'YYYY-MM-DD HH24:MI:SS.FF')) + extract(second FROM TO_TIMESTAMP(LAST_REFRESH_TIME, 'YYYY-MM-DD HH24:MI:SS.FF') - TO_TIMESTAMP(FIRST_REFRESH_TIME, 'YYYY-MM-DD HH24:MI:SS.FF')) REFRESH_TS,
  24 * 3600 * extract(day FROM TO_TIMESTAMP(LAST_CHANGE_TIME, 'YYYY-MM-DD HH24:MI:SS.FF') - TO_TIMESTAMP(FIRST_CHANGE_TIME, 'YYYY-MM-DD HH24:MI:SS.FF')) + 3600 * extract(hour FROM TO_TIMESTAMP(LAST_CHANGE_TIME, 'YYYY-MM-DD HH24:MI:SS.FF') - TO_TIMESTAMP(FIRST_CHANGE_TIME, 'YYYY-MM-DD HH24:MI:SS.FF')) + 60 * extract(minute FROM TO_TIMESTAMP(LAST_CHANGE_TIME, 'YYYY-MM-DD HH24:MI:SS.FF') - TO_TIMESTAMP(FIRST_CHANGE_TIME, 'YYYY-MM-DD HH24:MI:SS.FF')) + extract(second FROM TO_TIMESTAMP(LAST_CHANGE_TIME, 'YYYY-MM-DD HH24:MI:SS.FF') - TO_TIMESTAMP(FIRST_CHANGE_TIME, 'YYYY-MM-DD HH24:MI:SS.FF')) CHANGE_TS,
  OTHERSTAT_1_ID,
  OTHERSTAT_1_VALUE,
  OTHERSTAT_2_ID,/*20*/
  OTHERSTAT_2_VALUE,
  OTHERSTAT_3_ID,
  OTHERSTAT_3_VALUE,
  OTHERSTAT_4_ID,
  OTHERSTAT_4_VALUE,/*25*/
  OTHERSTAT_5_ID,
  OTHERSTAT_5_VALUE,
  OTHERSTAT_6_ID,
  OTHERSTAT_6_VALUE,
  TRUNC((DB_TIME)/1000000000.0/2.5, 4) MY_DB_TIME,
  TRUNC((DB_TIME-USER_IO_WAIT_TIME)/1000000000.0/2.5, 4) MY_CPU_TIME,
  TRUNC((USER_IO_WAIT_TIME)/1000000000.0/2.5, 4) MY_IO_TIME
FROM
  sys.gv$sql_plan_monitor
WHERE
    trace_id = 'YCB4C0BA2D919-0005F05350C624FB-0-0'
ORDER BY
  PLAN_LINE_ID ASC, SVR_IP, SVR_PORT, CHANGE_TS, PROCESS_NAME ASC;